﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace DBProvider.Providers
{
	public class CourseProvider : ICourseProvider
	{
		#region ICourseProvider Members

		public List<CourseCategory> GetAllCourseCategory()
		{
			List<CourseCategory> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.CourseCategories.ToList();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetCourseByCategory(int theCrsCateID, int drugStoreID)
		{
			if(theCrsCateID < 0 || drugStoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					if(theCrsCateID > 0)
					{
						var data = db.Courses.Where(c => c.DrugStoreID == drugStoreID && c.CourseCateogryID == theCrsCateID).OrderByDescending(c => c.CourseAddDate);

						if(data != null)
						{
							result = data.ToList();
						}
					}
					else
					{
						var data = db.Courses.Where(c => c.DrugStoreID == drugStoreID).OrderByDescending(c => c.CourseAddDate);

						if(data != null)
						{
							result = data.ToList();
						}
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public Course GetCourse(int theCourseID)
		{
			if(theCourseID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			Course result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.Courses.SingleOrDefault(c => c.CourseID == theCourseID);
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public Course CreateCourse(Course theNewCourse, out string msg)
		{
			if(theNewCourse == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;

			Course result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.Courses.Count(c => c.CourseName == theNewCourse.CourseName) > 0;

					if(isExist)
					{
						msg = "同名的课程已存在";
					}
					else
					{
						db.Courses.InsertOnSubmit(theNewCourse);

						db.SubmitChanges();

						result = db.Courses.SingleOrDefault(c => c.CourseName == theNewCourse.CourseName);
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteCourse(int theCourseID)
		{
			if(theCourseID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var tagSubmit = false;

					var courseChapter = db.CourseChapters.Where(cc => cc.CourseID == theCourseID);

					if(courseChapter != null)
					{
						db.CourseChapters.DeleteAllOnSubmit(courseChapter);
						tagSubmit = true;
					}

					var rltUserAndCourse = db.RLT_USERANDCOURCEs.Where(uc => uc.CourseID == theCourseID);

					if(rltUserAndCourse != null)
					{
						db.RLT_USERANDCOURCEs.DeleteAllOnSubmit(rltUserAndCourse);
						tagSubmit = true;
					}

					var dbRLT_CourseAndShareTo = db.RLT_CourseAndShareTos.Where(cs => cs.CourseID == theCourseID);

					if(dbRLT_CourseAndShareTo != null)
					{
						db.RLT_CourseAndShareTos.DeleteAllOnSubmit(dbRLT_CourseAndShareTo);
						tagSubmit = true;
					}

					var dbCourse = db.Courses.SingleOrDefault(c => c.CourseID == theCourseID);

					if(dbCourse != null)
					{
						db.Courses.DeleteOnSubmit(dbCourse);
						tagSubmit = true;
					}

					if(tagSubmit)
					{
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpDateCourse(Course theCourse)
		{
			if(theCourse == null || theCourse.CourseID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourse = db.Courses.SingleOrDefault(c => c.CourseID == theCourse.CourseID);

					if(dbCourse != null)
					{
						dbCourse.Clone(theCourse);

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public CourseCategory CreateCrsCategory(CourseCategory theCrsCat)
		{
			if(theCrsCat == null)
			{
				throw new ArgumentNullException();
			}

			CourseCategory result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.CourseCategories.InsertOnSubmit(theCrsCat);
					db.SubmitChanges();

					result = db.CourseCategories.SingleOrDefault(cc => cc.CourseCateogryID == theCrsCat.CourseCateogryID);
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteCourseCateogry(int theCrsCatID)
		{
			if(theCrsCatID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCrsCat = db.CourseCategories.SingleOrDefault(cc => cc.CourseCateogryID == theCrsCatID);

					if(dbCrsCat != null)
					{
						db.CourseCategories.DeleteOnSubmit(dbCrsCat);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateCourseCategory(CourseCategory theCrsCat)
		{
			if(theCrsCat == null || theCrsCat.CourseCateogryID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCrsCat = db.CourseCategories.SingleOrDefault(cc => cc.CourseCateogryID == theCrsCat.CourseCateogryID);

					if(dbCrsCat != null)
					{
						dbCrsCat.CourseCateogryName = theCrsCat.CourseCateogryName;
						dbCrsCat.CourseCateogryDescription = theCrsCat.CourseCateogryDescription;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetCourseByUser(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from c in db.Courses
										 where (from uc in db.RLT_USERANDCOURCEs
														where uc.UserID == userID
														select uc.CourseID).Contains(c.CourseID)
										 orderby c.CourseAddDate descending
										 select c;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<CourseChapter> GetCourseChapterByCourse(int courseID)
		{
			if(courseID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<CourseChapter> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from cc in db.CourseChapters
										 where cc.CourseID == courseID
										 select cc;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public CourseChapter CreateCourseChapter(CourseChapter theNewCourseChapter, out string msg)
		{
			if(theNewCourseChapter == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			CourseChapter result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.CourseChapters.Count(cc => cc.CourseID == theNewCourseChapter.CourseID && cc.CourseChapterName == theNewCourseChapter.CourseChapterName) > 0;

					if(isExist)
					{
						msg = "该课程中章节名已存在";
					}
					else
					{
						db.CourseChapters.InsertOnSubmit(theNewCourseChapter);
						db.SubmitChanges();

						result = db.CourseChapters.SingleOrDefault(cc => cc.CourseID == theNewCourseChapter.CourseID && cc.CourseChapterName == theNewCourseChapter.CourseChapterName);
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteCourseChapter(int courseChapterID)
		{
			if(courseChapterID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseChapter = db.CourseChapters.SingleOrDefault(cc => cc.CourseChapterID == courseChapterID);

					if(dbCourseChapter != null)
					{
						db.CourseChapters.DeleteOnSubmit(dbCourseChapter);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public CourseChapter GetCourseChapter(int courseChapterID)
		{
			if(courseChapterID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			CourseChapter result = null;

			using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
			{
				result = db.CourseChapters.SingleOrDefault(cc => cc.CourseChapterID == courseChapterID);
			}

			return result;
		}

		public List<Course> GetCourseByCourseIDs(List<int> courseIDs)
		{
			if(courseIDs == null || courseIDs.Count <= 0)
			{
				throw new ArgumentException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from c in db.Courses
										 where courseIDs.Contains(c.CourseID)
										 select c;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void AddRLTUserAndCourse(List<RLT_USERANDCOURCE> lstUserAndCourse)
		{
			if(lstUserAndCourse != null && lstUserAndCourse.Count > 0)
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_USERANDCOURCEs.InsertAllOnSubmit(lstUserAndCourse);
					db.SubmitChanges();
				}
			}
		}

		public List<RLT_USERANDCOURCE> GetRLTUserAndCourseByUser(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_USERANDCOURCE> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from uc in db.RLT_USERANDCOURCEs
										 where uc.UserID == userID
										 select uc;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<CourseWare> GetAllCourseWare(int drugStoreID)
		{
			if(drugStoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<CourseWare> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from cw in db.CourseWares
										 where cw.DrugStoreID == drugStoreID
										 orderby cw.CourseWareAddTime descending
										 select cw;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public CourseWare GetCourseWare(int theCourseWareID)
		{
			if(theCourseWareID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			CourseWare result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.CourseWares.SingleOrDefault(cw => cw.CourseWareID == theCourseWareID);
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public CourseWare CreateCourseWare(CourseWare theNewCourseWare, out string msg)
		{
			if(theNewCourseWare == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			CourseWare result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.CourseWares.Count(cw => cw.CourseWareName == theNewCourseWare.CourseWareName) > 0;

					if(!isExist)
					{
						db.CourseWares.InsertOnSubmit(theNewCourseWare);
						db.SubmitChanges();

						result = db.CourseWares.SingleOrDefault(cw => cw.CourseWareName == theNewCourseWare.CourseWareName);
					}
					else
					{
						msg = "课件名已存在";
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateCourseWare(CourseWare theCourseWare)
		{
			if(theCourseWare == null || theCourseWare.CourseWareID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseWare = db.CourseWares.SingleOrDefault(cw => cw.CourseWareID == theCourseWare.CourseWareID);

					if(dbCourseWare != null)
					{
						dbCourseWare.CourseWareDescription = theCourseWare.CourseWareDescription;
						dbCourseWare.CourseWareName = theCourseWare.CourseWareName;
						dbCourseWare.CourseWarePath = theCourseWare.CourseWarePath;
						dbCourseWare.CourseWareSize = theCourseWare.CourseWareSize;
						dbCourseWare.DownloadCount = theCourseWare.DownloadCount;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteCourseWare(int theCourseWareID)
		{
			if(theCourseWareID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseWare = db.CourseWares.SingleOrDefault(cw => cw.CourseWareID == theCourseWareID);

					if(dbCourseWare != null)
					{
						db.CourseWares.DeleteOnSubmit(dbCourseWare);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteCourseWare(List<int> ids)
		{
			if(ids == null || ids.Count <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseWare = from cw in db.CourseWares
														 where ids.Contains(cw.CourseWareID)
														 select cw;

					if(dbCourseWare != null)
					{
						db.CourseWares.DeleteAllOnSubmit(dbCourseWare);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateRLT_UserAndCourse(RLT_USERANDCOURCE theRLT_UserAndCourse)
		{
			if(theRLT_UserAndCourse == null || theRLT_UserAndCourse.CourseID <= 0 || theRLT_UserAndCourse.UserID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbRLT_UserAndCourse = db.RLT_USERANDCOURCEs.SingleOrDefault(uc => uc.UserID == theRLT_UserAndCourse.UserID && uc.CourseID == theRLT_UserAndCourse.CourseID);

					if(dbRLT_UserAndCourse != null)
					{
						dbRLT_UserAndCourse.LastOperateTime = theRLT_UserAndCourse.LastOperateTime;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<RLT_USERANDCOURCE> GetRLT_UserAndCourseByUser(int userID, DateTime operateTimeStart, DateTime operateTimeEnd)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_USERANDCOURCE> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_USERANDCOURCE>(uc => uc.Course);
					db.LoadOptions = dlo;

					var data = from uc in db.RLT_USERANDCOURCEs
										 where uc.UserID == userID && uc.LastOperateTime >= operateTimeStart && uc.LastOperateTime <= operateTimeEnd
										 select uc;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetNewCourse(int count, int drugstoreID)
		{
			if(count <= 0 || drugstoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					var data = from c in db.Courses
										 where c.DrugStoreID == drugstoreID
										 orderby c.CourseAddDate descending
										 select c;

					if(data != null)
					{
						result = data.Take(count).ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetCourseByCategory(int theCrsCateID, int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if(theCrsCateID < 0 || drugStoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					if(theCrsCateID > 0)
					{
						totalRecords = db.Courses.Count(c => c.DrugStoreID == drugStoreID && c.CourseCateogryID == theCrsCateID);

						var data = db.Courses.Where(c => c.DrugStoreID == drugStoreID && c.CourseCateogryID == theCrsCateID).OrderByDescending(c => c.CourseAddDate);

						if(data != null)
						{
							result = data.ToList();
						}
					}
					else
					{
						totalRecords = db.Courses.Count(c => c.DrugStoreID == drugStoreID);

						var data = db.Courses.Where(c => c.DrugStoreID == drugStoreID).OrderByDescending(c => c.CourseAddDate);

						if(data != null)
						{
							result = data.ToList();
						}
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<RLT_CourseAndShareTo> GetCourseAndShareToByCourse(int courseID)
		{
			if(courseID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_CourseAndShareTo> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_CourseAndShareTo>(ms => ms.User);
					db.LoadOptions = dlo;

					var data = from vs in db.RLT_CourseAndShareTos
										 where vs.CourseID == courseID
										 select vs;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void CreateCourseAndShareTo(List<RLT_CourseAndShareTo> lstCourseAndShareTo)
		{
			if(lstCourseAndShareTo == null || lstCourseAndShareTo.Count <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_CourseAndShareTos.InsertAllOnSubmit(lstCourseAndShareTo);
					db.SubmitChanges();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public void SaveCourseAndShareTo(int courseID, List<RLT_CourseAndShareTo> lstCourseAndShareTo)
		{
			if(courseID <= 0 || lstCourseAndShareTo == null)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseAndShareTo = from vs in db.RLT_CourseAndShareTos
																	 where vs.CourseID == courseID
																	 select vs;

					if(dbCourseAndShareTo != null)
					{
						var lstDBCourseAndShareTo = dbCourseAndShareTo.ToList();

						var deleteItems = lstDBCourseAndShareTo.Except(lstCourseAndShareTo);

						if(deleteItems != null)
						{
							db.RLT_CourseAndShareTos.DeleteAllOnSubmit(deleteItems);
						}

						var newItems = lstCourseAndShareTo.Except(lstDBCourseAndShareTo);

						if(newItems != null)
						{
							db.RLT_CourseAndShareTos.InsertAllOnSubmit(newItems);
						}

						db.SubmitChanges();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public List<Course> GetCourseByCategory(int userID, int theCrsCateID, int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if(theCrsCateID < 0 || drugStoreID <= 0 || userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					if(theCrsCateID > 0)
					{
						totalRecords = (from c in db.Courses
														where c.CourseCateogryID == theCrsCateID && ((c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																																																			 where cs.ShareToDrugStoreID == user.DrugStoreID
																																																																			 select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																																											where cs.ShareToMemberID == userID
																																																																																											select cs.CourseID).Contains(c.CourseID)))
														select c).Count();

						var data = (from c in db.Courses
												where c.CourseCateogryID == theCrsCateID && ((c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																																																	 where cs.ShareToDrugStoreID == user.DrugStoreID
																																																																	 select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																																									where cs.ShareToMemberID == userID
																																																																																									select cs.CourseID).Contains(c.CourseID)))
												orderby c.CourseAddDate descending
												select c).Skip(pageIndex * pageSize).Take(pageSize);

						if(data != null)
						{
							result = data.ToList();
						}
					}
					else
					{
						totalRecords = (from c in db.Courses
														where (c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																																where cs.ShareToDrugStoreID == user.DrugStoreID
																																																select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																							 where cs.ShareToMemberID == userID
																																																																							 select cs.CourseID).Contains(c.CourseID))
														select c).Count();

						var data = (from c in db.Courses
												where (c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																														where cs.ShareToDrugStoreID == user.DrugStoreID
																																														select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																					 where cs.ShareToMemberID == userID
																																																																					 select cs.CourseID).Contains(c.CourseID))
												orderby c.CourseAddDate descending
												select c).Skip(pageIndex * pageSize).Take(pageSize);

						if(data != null)
						{
							result = data.ToList();
						}
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetNewCourse(int count, int drugstoreID, int userID)
		{
			if(count <= 0 || drugstoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					var data = from c in db.Courses
										 //where c.DrugStoreID == drugstoreID
										 where ((c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																													where cs.ShareToDrugStoreID == user.DrugStoreID
																																													select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																				 where cs.ShareToMemberID == userID
																																																																				 select cs.CourseID).Contains(c.CourseID)))
										 orderby c.CourseAddDate descending
										 select c;

					if(data != null)
					{
						result = data.Take(count).ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetNewCourseByCategory(int userID, int theCrsCateID, int drugStoreID, int count)
		{
			if(count <= 0 || drugStoreID <= 0 || userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					var data = from c in db.Courses
										 //where c.DrugStoreID == drugstoreID
										 where c.CourseCateogryID == theCrsCateID && ((c.DrugStoreID == user.DrugStoreID) || (c.IsPublic == 1) || ((from cs in db.RLT_CourseAndShareTos
																																																																where cs.ShareToDrugStoreID == user.DrugStoreID
																																																																select cs.CourseID).Contains(c.CourseID)) || ((from cs in db.RLT_CourseAndShareTos
																																																																																							 where cs.ShareToMemberID == userID
																																																																																							 select cs.CourseID).Contains(c.CourseID)))
										 orderby c.CourseAddDate descending
										 select c;

					if(data != null)
					{
						result = data.Take(count).ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Course> GetNewCourseByCategory(int theCrsCateID, int drugStoreID, int count)
		{
			if(count <= 0 || drugStoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Course> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<Course>(c => c.CourseChapters);
					db.LoadOptions = dlo;

					var data = from c in db.Courses
										 where c.DrugStoreID == drugStoreID && c.CourseCateogryID == theCrsCateID
										 orderby c.CourseAddDate descending
										 select c;

					if(data != null)
					{
						result = data.Take(count).ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<RLT_CourseWareAndShareTo> GetCourseWareAndShareToByCourse(int courseWareID)
		{
			if(courseWareID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_CourseWareAndShareTo> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_CourseWareAndShareTo>(cws => cws.User);
					db.LoadOptions = dlo;

					var data = from vs in db.RLT_CourseWareAndShareTos
										 where vs.CourseWareID == courseWareID
										 select vs;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void CreateCourseWareAndShareTo(List<RLT_CourseWareAndShareTo> lstCourseWareAndShareTo)
		{
			if(lstCourseWareAndShareTo == null || lstCourseWareAndShareTo.Count <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_CourseWareAndShareTos.InsertAllOnSubmit(lstCourseWareAndShareTo);
					db.SubmitChanges();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public void SaveCourseWareAndShareTo(int courseWareID, List<RLT_CourseWareAndShareTo> lstCourseWareAndShareTo)
		{
			if(courseWareID <= 0 || lstCourseWareAndShareTo == null)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbCourseWareAndShareTo = from vs in db.RLT_CourseWareAndShareTos
																			 where vs.CourseWareID == courseWareID
																			 select vs;

					if(dbCourseWareAndShareTo != null)
					{
						var lstDBCourseWareAndShareTo = dbCourseWareAndShareTo.ToList();

						var deleteItems = lstDBCourseWareAndShareTo.Except(lstCourseWareAndShareTo);

						if(deleteItems != null)
						{
							db.RLT_CourseWareAndShareTos.DeleteAllOnSubmit(deleteItems);
						}

						var newItems = lstCourseWareAndShareTo.Except(lstDBCourseWareAndShareTo);

						if(newItems != null)
						{
							db.RLT_CourseWareAndShareTos.InsertAllOnSubmit(newItems);
						}

						db.SubmitChanges();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public List<CourseWare> GetAllCourseWare(int userID, int drugStoreID)
		{
			if(drugStoreID <= 0 || userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<CourseWare> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					var data = from cw in db.CourseWares
										 where (cw.DrugStoreID == user.DrugStoreID) || (cw.IsPublic == 1) || ((from cws in db.RLT_CourseWareAndShareTos
																																													 where cws.ShareToDrugStoreID == user.DrugStoreID
																																													 select cws.CourseWareID).Contains(cw.CourseWareID)) || ((from cws in db.RLT_CourseWareAndShareTos
																																																																										where cws.ShareToMemberID == userID
																																																																										select cws.CourseWareID).Contains(cw.CourseWareID))
										 orderby cw.CourseWareAddTime descending
										 select cw;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion
	}
}
